﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;

namespace EMR_System.Data
{
    public class DatabaseSupport
    {
        public static bool Login(User user)
        {
            DataAccessSQL DAS = new Data.DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "SELECT Password FROM [User] WHERE Username = '" + user.Username + "'");
            string passwd = DAS.ExecuteReader(sqlCmd, DAS.GetConnectionString());

            if (passwd == null)
                return false;

            return Convert.ToBoolean(passwd.Equals(user.Password));
        }

        public static bool ValidateUser(User user)
        {
            return false;
        }

        public static List<User> GetUserList(int type)
        {
            List<User> users = new List<User>();

            DataAccessSQL DAS = new Data.DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "SELECT Username, FirstName, LastName, Password, Type FROM [User] WHERE Type = " + type);

            List<string>[] result = DAS.ExecuteReaderListString(sqlCmd, DAS.GetConnectionString());

            foreach (List<string> user in result)
            {
                string username = user[0];
                string firstName = user[1];
                string lastName = user[2];
                string password = user[3];
                int tmpType = Convert.ToInt32(user[4]);

                User temp = new User(username, password);
                temp.FirstName = firstName;
                temp.LastName = lastName;

                if (tmpType == 1)
                    temp.Type = UserType.Receptionist;
                else if (tmpType == 2)
                    temp.Type = UserType.Nurse;
                else if (tmpType == 3)
                    temp.Type = UserType.Doctor;

                users.Add(temp);
            }

            return users;
        }

        public static User GetUser(string username)
        {
            User user = null;

            DataAccessSQL DAS = new DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "SELECT FirstName, LastName, Password, Type FROM [User] WHERE Username = '" + username + "'");

            List<string>[] result = DAS.ExecuteReaderListString(sqlCmd, DAS.GetConnectionString());

            if (result.Length > 0)
            {
                string firstName = result[0][0];
                string lastName = result[0][1];
                string password = result[0][2];
                int type = Convert.ToInt32(result[0][3]);

                user = new User(username, password);
                user.FirstName = firstName;
                user.LastName = lastName;

                if (type == 1)
                    user.Type = UserType.Receptionist;
                else if (type == 2)
                    user.Type = UserType.Nurse;
                else
                    user.Type = UserType.Doctor;
            }

            return user;
        }

        public static int CreatePatient(Patient p)
        {
            int emergencyContact = AddEmergencyContact(p.EmergencyContact);
            int employer = AddEmployerInformation(p.Employer);
            int insurance = AddInsuranceInformation(p.Insurance);

            DataAccessSQL DAS = new Data.DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "INSERT INTO [Patient] (SSN, FirstName, LastName, Address, PhoneNumber, DateOfBirth, Insurance, EmergencyContact, Employer) VALUES (@ssn, @firstName, @lastName, @address, @phone, @dob, @insurance, @emergencyContact, @employer)");

            sqlCmd.Parameters.AddWithValue("@ssn", p.SSN);
            sqlCmd.Parameters.AddWithValue("@firstName", p.FirstName);
            sqlCmd.Parameters.AddWithValue("@lastName", p.LastName);
            sqlCmd.Parameters.AddWithValue("@address", p.Address);
            sqlCmd.Parameters.AddWithValue("@phone", p.PhoneNumber);
            sqlCmd.Parameters.AddWithValue("@dob", p.DateOfBirth);
            sqlCmd.Parameters.AddWithValue("@insurance", insurance);
            sqlCmd.Parameters.AddWithValue("@emergencyContact", emergencyContact);
            sqlCmd.Parameters.AddWithValue("@employer", employer);
         
            return DAS.ExecuteScalar(sqlCmd, DAS.GetConnectionString());
        }

        public static int AddEmergencyContact(EmergencyContact contact)
        {
            int ID = -1;

            DataAccessSQL DAS = new DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "INSERT INTO [EmergencyContact] (FirstName, LastName, PhoneNumber, Relation) Values (@firstName, @lastName, @phoneNumber, @relation)");

            DAS.AddParamToSQLCmd(sqlCmd, "@firstName", SqlDbType.NVarChar, 100, ParameterDirection.Input, contact.FirstName);
            DAS.AddParamToSQLCmd(sqlCmd, "@lastname", SqlDbType.NVarChar, 100, ParameterDirection.Input, contact.LastName);
            DAS.AddParamToSQLCmd(sqlCmd, "@phoneNumber", SqlDbType.NVarChar, 100, ParameterDirection.Input, contact.PhoneNumber);
            DAS.AddParamToSQLCmd(sqlCmd, "@relation", SqlDbType.NVarChar, 100, ParameterDirection.Input, contact.Relation);

            ID = DAS.ExecuteScalar(sqlCmd, DAS.GetConnectionString());

            if (ID == -1)
                throw new Exception("AddEmergencyContact: ID is still -1");

            return ID;
        }

        public static int AddEmployerInformation(EmployerInformation employer)
        {
            int ID = -1;

            DataAccessSQL DAS = new DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "INSERT INTO [EmployerInformation] (Name, PhoneNumber) Values (@name, @phone)");

            DAS.AddParamToSQLCmd(sqlCmd, "@name", SqlDbType.NVarChar, 100, ParameterDirection.Input, employer.Name);
            DAS.AddParamToSQLCmd(sqlCmd, "@phone", SqlDbType.NVarChar, 100, ParameterDirection.Input, employer.PhoneNumber);

            ID = DAS.ExecuteScalar(sqlCmd, DAS.GetConnectionString());

            if (ID == -1)
                throw new Exception("AddEmployerInformation: ID is still -1");

            return ID;
        }

        public static int AddInsuranceInformation(InsuranceInformation insurance)
        {
            int ID = -1;

            DataAccessSQL DAS = new DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "INSERT INTO [InsuranceInformation] (Provider, Address, PhoneNumber, PolicyNumber, GroupNumber) Values (@provider, @address, @phone, @policyNum, @groupNum)");

            DAS.AddParamToSQLCmd(sqlCmd, "@provider", SqlDbType.Int, 4, ParameterDirection.Input, insurance.Provider);
            DAS.AddParamToSQLCmd(sqlCmd, "@address", SqlDbType.NVarChar, 100, ParameterDirection.Input, insurance.Address);
            DAS.AddParamToSQLCmd(sqlCmd, "@phone", SqlDbType.NVarChar, 100, ParameterDirection.Input, insurance.PhoneNumber);
            DAS.AddParamToSQLCmd(sqlCmd, "@policyNum", SqlDbType.NVarChar, 100, ParameterDirection.Input, insurance.PolicyNumber);
            DAS.AddParamToSQLCmd(sqlCmd, "@groupNum", SqlDbType.NVarChar, 100, ParameterDirection.Input, insurance.GroupNumber);

            ID = DAS.ExecuteScalar(sqlCmd, DAS.GetConnectionString());

            if (ID == -1)
                throw new Exception("AddInsuranceInformation: ID is still -1");

            return ID;
        }

        public static Patient GetPatient(int ID)
        {
            Patient p = null;

            DataAccessSQL DAS = new Data.DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "SELECT SSN, FirstName, LastName, Address, PhoneNumber, DateOfBirth, Insurance, EmergencyContact, Employer FROM [Patient] WHERE ID = " + ID);

            List<string>[] result = DAS.ExecuteReaderListString(sqlCmd, DAS.GetConnectionString());

            if (result.Length > 0)
            {
                string ssn = result[0][0];
                string firstName = result[0][1];
                string lastName = result[0][2];
                string address = result[0][3];
                string phoneNumber = result[0][4];
                DateTime dob = Convert.ToDateTime(result[0][5]);
                int insuranceID = Convert.ToInt32(result[0][6]);
                int emergencyContactID = Convert.ToInt32(result[0][7]);
                int employerID = Convert.ToInt32(result[0][8]);

                InsuranceInformation insuranceInfo = GetInsuranceInformation(insuranceID);
                EmergencyContact emergencyContact = GetEmergencyContact(emergencyContactID);
                EmployerInformation employerInfo = GetEmployerInformation(employerID);

                p = new Patient(firstName, lastName, address, phoneNumber, ssn, dob, insuranceInfo, emergencyContact, employerInfo);
                p.ID = ID;
            }

            return p;
        }

        public static List<Patient> GetPatientList()
        {
            List<Patient> patients = new List<Patient>();

            DataAccessSQL DAS = new Data.DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "SELECT ID, SSN, FirstName, LastName, Address, PhoneNumber, DateOfBirth, Insurance, EmergencyContact, Employer FROM [Patient]");

            List<string>[] result = DAS.ExecuteReaderListString(sqlCmd, DAS.GetConnectionString());

            foreach (List<string> patient in result)
            {
                int ID = Convert.ToInt32(patient[0]);
                string ssn = patient[1];
                string firstName = patient[2];
                string lastName = patient[3];
                string address = patient[4];
                string phoneNumber = patient[5];
                DateTime dob = Convert.ToDateTime(patient[6]);
                int insuranceID = Convert.ToInt32(patient[7]);
                int emergencyContactID = Convert.ToInt32(patient[8]);
                int employerID = Convert.ToInt32(patient[9]);

                InsuranceInformation insuranceInfo = GetInsuranceInformation(insuranceID);
                EmergencyContact emergencyContact = GetEmergencyContact(emergencyContactID);
                EmployerInformation employerInfo = GetEmployerInformation(employerID);

                Patient temp = new Patient(firstName, lastName, address, phoneNumber, ssn, dob, insuranceInfo, emergencyContact, employerInfo);
                temp.ID = ID;

                patients.Add(temp);
            }

            return patients;
        }

        private static InsuranceInformation GetInsuranceInformation(int ID)
        {
            InsuranceInformation insuranceInfo = null;

            DataAccessSQL DAS = new Data.DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "SELECT Provider, Address, PhoneNumber, PolicyNumber, GroupNumber FROM [InsuranceInformation] WHERE ID = " + ID);

            List<string>[] result = DAS.ExecuteReaderListString(sqlCmd, DAS.GetConnectionString());

            int providerID = Convert.ToInt32(result[0][0]);
            string address = result[0][1];
            string phoneNumber = result[0][2];
            string policyNumber = result[0][3];
            string groupNumber = result[0][4];

            InsuranceProvider provider;

            if (providerID == 1)
                provider = InsuranceProvider.BCBS;
            else if (providerID == 2)
                provider = InsuranceProvider.Principal;
            else if (providerID == 3)
                provider = InsuranceProvider.Medicare;
            else
                provider = InsuranceProvider.Medicaid;

            insuranceInfo = new InsuranceInformation(provider, address, phoneNumber, policyNumber, groupNumber);

            return insuranceInfo;
        }

        private static EmergencyContact GetEmergencyContact(int ID)
        {
            EmergencyContact emergencyContact = null;

            DataAccessSQL DAS = new Data.DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "SELECT FirstName, LastName, PhoneNumber, Relation FROM [EmergencyContact] WHERE ID = " + ID);

            List<string>[] result = DAS.ExecuteReaderListString(sqlCmd, DAS.GetConnectionString());

            string firstName = result[0][0];
            string lastName = result[0][1];
            string phoneNumber = result[0][2];
            string relation = result[0][3];

            emergencyContact = new EmergencyContact(firstName, lastName, phoneNumber, relation);

            return emergencyContact;
        }

        private static EmployerInformation GetEmployerInformation(int ID)
        {
            EmployerInformation employerInfo = null;

            DataAccessSQL DAS = new Data.DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "SELECT Name, PhoneNumber FROM [EmployerInformation] WHERE ID = " + ID);

            List<string>[] result = DAS.ExecuteReaderListString(sqlCmd, DAS.GetConnectionString());

            string name = result[0][0];
            string phoneNumber = result[0][1];

            employerInfo = new EmployerInformation(name, phoneNumber);

            return employerInfo;
        }

        public static bool UpdatePatient()
        {
            return true;
        }

        public static Patient DeletePatient()
        {
            return null;
        }

        public static int CreateAppointment(Appointment a)
        {
            int ID = -1;

            DataAccessSQL DAS = new DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "INSERT INTO [Appointment] (PatientID, Doctor, Date, IsNewPatient, Duration, Reason) VALUES(@patientID, @doctor, @date, @isNewPatient, @duration, @reason)");

            DAS.AddParamToSQLCmd(sqlCmd, "@patientID", SqlDbType.Int, 4, ParameterDirection.Input, a.PatientID);
            DAS.AddParamToSQLCmd(sqlCmd, "@doctor", SqlDbType.NVarChar, 100, ParameterDirection.Input, a.Doctor);
            DAS.AddParamToSQLCmd(sqlCmd, "@date", SqlDbType.DateTime, 8, ParameterDirection.Input, a.Date);
            DAS.AddParamToSQLCmd(sqlCmd, "@isNewPatient", SqlDbType.NVarChar, 100, ParameterDirection.Input, a.IsNewPatient);
            DAS.AddParamToSQLCmd(sqlCmd, "@duration", SqlDbType.Int, 4, ParameterDirection.Input, a.Duration);
            DAS.AddParamToSQLCmd(sqlCmd, "@reason", SqlDbType.NVarChar, 100, ParameterDirection.Input, a.Reason);

            ID = DAS.ExecuteScalar(sqlCmd, DAS.GetConnectionString());

            if (ID == -1)
                throw new Exception("CreateAppointment: ID is still -1");

            return ID;
        }

        // Iteration 3
        public static Appointment GetAppointment(int ID)
        {
            Appointment a = null;

            DataAccessSQL DAS = new DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "SELECT PatientID, Doctor, Date, IsNewPatient, Duration, Reason FROM [Appointment] WHERE ID = " + ID);

            List<string>[] result = DAS.ExecuteReaderListString(sqlCmd, DAS.GetConnectionString());

            if (result.Length > 0)
            {
                int patientID = Convert.ToInt32(result[0][0]);
                string doctor = result[0][1];
                DateTime tmpDate = Convert.ToDateTime(result[0][2]);
                bool isNewPatient = Convert.ToBoolean(result[0][3]);
                int duration = Convert.ToInt32(result[0][4]);
                string reason = result[0][5];

                a = new Appointment(patientID, doctor, tmpDate, isNewPatient, duration, reason);
                a.ID = ID;
            }

            return a;
        }

        public static List<Appointment> GetAppointmentList(DateTime date, bool searchFuture)
        {
            List<Appointment> appts = new List<Appointment>();

            DataAccessSQL DAS = new DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "SELECT ID, PatientID, Doctor, Date, IsNewPatient, Duration, Reason FROM [Appointment]");

            List<string>[] result = DAS.ExecuteReaderListString(sqlCmd, DAS.GetConnectionString());

            // Create appt list from database
            foreach (List<string> appt in result)
            {
                int ID = Convert.ToInt32(appt[0]);
                int patientID = Convert.ToInt32(appt[1]);
                string doctor = appt[2];
                DateTime tmpDate = Convert.ToDateTime(appt[3]);
                bool isNewPatient = Convert.ToBoolean(appt[4]);
                int duration = Convert.ToInt32(appt[5]);
                string reason = appt[6];

                Appointment a = new Appointment(patientID, doctor, tmpDate, isNewPatient, duration, reason);
                a.ID = ID;

                appts.Add(a);
            }

            // Search list for appointments based on the given parameters
            List<Appointment> retList = new List<Appointment>();

            foreach (Appointment appt in appts)
            {
                if ((searchFuture) ? appt.Date.Date >= date.Date : appt.Date.Date == date.Date)
                    retList.Add(appt);
            }

            return retList;
        }

        public static bool UpdateAppointment()
        {
            return true;
        }

        public static Appointment DeleteAppointment()
        {
            return null;
        }

        // Iteration 3
        public static int AddNote(int Type, int ApptID)
        {
            int ID = -1;

            DataAccessSQL DAS = new DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "INSERT INTO [Note] (AppointmentID, Type) Values (@apptID, @type)");

            DAS.AddParamToSQLCmd(sqlCmd, "@apptID", SqlDbType.Int, 4, ParameterDirection.Input, ApptID);
            DAS.AddParamToSQLCmd(sqlCmd, "@type", SqlDbType.Int, 4, ParameterDirection.Input, Type);

            ID = DAS.ExecuteScalar(sqlCmd, DAS.GetConnectionString());

            if (ID == -1)
                throw new Exception("AddNote: ID is still -1");

            return ID;
        }

        // Iteration 3
        public static bool AddVitals(int NoteID, Vitals v)
        {
            DataAccessSQL DAS = new DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "UPDATE [Note] SET Height=@height, Weight=@weight, Temp=@temp, Pulse=@pulse, Respirations=@respirations, BloodPressure=@bloodPressure WHERE ID = " + NoteID);

            DAS.AddParamToSQLCmd(sqlCmd, "@height", SqlDbType.Int, 4, ParameterDirection.Input, v.Height);
            DAS.AddParamToSQLCmd(sqlCmd, "@weight", SqlDbType.Float, 8, ParameterDirection.Input, v.Weight);
            DAS.AddParamToSQLCmd(sqlCmd, "@temp", SqlDbType.Float, 8, ParameterDirection.Input, v.Temp);
            DAS.AddParamToSQLCmd(sqlCmd, "@pulse", SqlDbType.Int, 4, ParameterDirection.Input, v.Pulse);
            DAS.AddParamToSQLCmd(sqlCmd, "@respirations", SqlDbType.Int, 4, ParameterDirection.Input, v.Respirations);
            DAS.AddParamToSQLCmd(sqlCmd, "@bloodPressure", SqlDbType.NVarChar, 100, ParameterDirection.Input, v.BloodPressure);

            return DAS.ExecuteNonQuery(sqlCmd, DAS.GetConnectionString());
        }

        // Iteration 3
        public static bool AddComplaint(int NoteID, string complaint)
        {
            DataAccessSQL DAS = new DataAccessSQL();
            SqlCeCommand sqlCmd = new SqlCeCommand();
            DAS.SetCommandType(sqlCmd, CommandType.Text, "UPDATE [Note] SET Complaint = '" + complaint + "' WHERE ID = " + NoteID);

            return DAS.ExecuteNonQuery(sqlCmd, DAS.GetConnectionString());
        }
    }
}
